package com.jeasonzhao.report.tool;

import java.util.Vector;

import com.jeasonzhao.commons.db.Sqlca;
import com.jeasonzhao.commons.db.helper.ConnectionHelper;

public class DemoDatabaseGenerator
{
    public DemoDatabaseGenerator()
    {
        super();
    }

    public static void main(String[] argvs)
        throws Exception
    {
        Sqlca sqlca = new Sqlca(ConnectionHelper.connectSqlite("d:\\sqlreport.version4.demodata.sqlitedb"));
        //Sqlca sqlca = new Sqlca(ConnectionHelper.connectSqlServer("localhost","SQLReportDemo","sa","123456"));
        try
        {
            String[] kpis = new String[]
                {"猪肉","鸡蛋","鱼类","蔬菜","水果"};
            String[] Citys = "河北|山西|陕西|重庆|四川|广东|海南".split("\\|");
            //if exists (select 1 from sysobjects where id = object_id('CityCodes') and type = 'U')
            sqlca.executeDiscardException("drop table CityCodes");
            sqlca.executeDiscardException("drop table Kpi_define");
            sqlca.executeDiscardException("drop table Kpi_values");
            sqlca.execute("create table CityCodes(cityid int not null primary key,cityname varchar(128) not null,parentCode int)");
            sqlca.execute("create table Kpi_define(kpiid int not null primary key,kpiname varchar(128) not null,kpidesc varchar(128))");
            sqlca.execute("create table Kpi_values(kpiid int not null,cityid int not null,kpidate datetime not null," +
                          "kpivalue numeric(12,2),kpivalue2 numeric(12,2),kpivalue3 numeric(12,2),kpivalue4 numeric(12,2))");
            for(int n = 1;n < Citys.length;n++)
            {
                String strCityName = Citys[n];
                int nProvinceId = n;
                sqlca.execute("insert into CityCodes values(?,?,?)",new Object[]
                              {new Integer(nProvinceId),strCityName,(Integer)null});
                for(int nSub = 1;nSub <= 5;nSub++)
                {
                    int nCityID = n * 10000 + nSub * 100;
                    sqlca.execute("insert into CityCodes values(?,?,?)",new Object[]
                                  {new Integer(nCityID),strCityName + "_地市" + nSub,new Integer(nProvinceId)});
                    for(int nSub2 = 1;nSub2 <= 5;nSub2++)
                    {
                        sqlca.execute("insert into CityCodes values(?,?,?)",new Object[]
                                      {new Integer(n * 100000000 + +nSub * 10000 + nSub2),strCityName + "_地市" + nSub + "_县" + nSub2,
                                      new Integer(nCityID)});
                    }
                }
            }
//            if(true)
//            {
//                return;
//            }
            Vector<Integer> vect = new Vector<Integer>();
            sqlca.execute("select cityid from CityCodes");
            while(sqlca.next())
            {
                vect.add(sqlca.getInteger("cityid"));
            }
            java.util.Calendar ca = java.util.Calendar.getInstance();
            for(int nKpiId = 0;nKpiId < kpis.length;nKpiId++)
            {
                sqlca.beginTransaction();
                System.out.println("处理 " + (nKpiId + 1) + "/" + kpis.length + "  " + kpis[nKpiId]);
                sqlca.execute("insert into kpi_define values(?,?,?)",
                              new Object[]
                              {new Integer(nKpiId),kpis[nKpiId],"Description:" + kpis[nKpiId]});
                ca.setTime(new java.util.Date());
                ca.add(java.util.Calendar.DATE, -60);
                for(int nDate = 0;nDate < 120;nDate++)
                {
                    ca.add(java.util.Calendar.DATE,1);
                    for(int nc = 0;nc < vect.size();nc++)
                    {
                        if(nDate > 1 && (((Integer) (vect.elementAt(nc))).intValue() % nDate) == 0)
                        {
                            continue;
                        }

//                        int nseed = r.nextInt();
//                        for(int n = 0;n < nseed;n++)
//                        {
//                            Math.random();
//                        }
                        double value = 100 * ((Math.random() * 10));
                        double value2 = 100 * ((Math.random() * 100));
                        double value3 = 100 * ((Math.random() * 100));
                        double value4 = 100 * ((Math.random() * 10));
                        System.out.print('.');
                        sqlca.execute("insert into Kpi_values values(?,?,?,?,?,?,?)",
                                      new Object[]
                                      {
                                      new Integer(nKpiId),vect.elementAt(nc),ca.getTime(),
                                      new Double(value),
                                      new Double(value2)
                                      ,new Double(value3),new Double(value4)
                        });
                    }
                }
                sqlca.commit();
            }
        }
        catch(Exception excep)
        {
            throw excep;
        }
        finally
        {
            sqlca.closeAll();
        }
    }

}
